/******************************************************************

  
    All rights reserved
    Confidential and Proprietary

*******************************************************************
File Name		     : SqlHelper.cs
Project Code		 : 
Author  			 : 
Created on	    	 : 
*******************************************************************
Type		    	 : C# class file
Purpose			     : This class has methods for database access
Pre-requisites		 : 
Known Bugs		     : 
Associated File(s)	 : None
Table(s)/Procedure(s): None
Method(s)    		 : ExecuteNonQuery,ExecuteScalar,ExecuteReader,
                       ExecuteDataSet,ExecuteDataTable,
                       BeginTransaction,CommitTransaction,RollbackTransaction
See Also			 :
********************************************************************/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Configuration;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Data.OracleClient;
using System.Data.OleDb;
using System.Data.Odbc;
using System.IO;

/******************************************************************

   
    All rights reserved
    Confidential and Proprietary

*******************************************************************
File Name		     : SqlHelper.cs
Project Code		 : 
Author  			 : 
Created on	    	 : 
*******************************************************************
Type		    	 : C# class file
Purpose			     : This class has methods for database access
Pre-requisites		 : 
Known Bugs		     : 
Associated File(s)	 : None
Table(s)/Procedure(s): None
Method(s)    		 : ExecuteNonQuery,ExecuteScalar,ExecuteReader,
                       ExecuteDataSet,ExecuteDataTable,
                       BeginTransaction,CommitTransaction,RollbackTransaction
See Also			 :
******************************************************************/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Configuration;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Data.OleDb;
using System.Data.Odbc;
using System.IO;



namespace DBHelper
{
    /// <summary>
    /// The SqlHelper class utilizes the features of ADO.NET 2.0 
    /// to give you a generic data access layer.
    /// SqlHelper needs to be instantiated and there are 4 overloads per method
    /// You do not need to create any connection object in your code. 
    /// Simply indicate whether you would like to keep the connection open via 
    /// ConnectionState enumeration. 
    /// If you do not specify this option, the underlying connection is closed.
    /// </summary>
    public class SqlHelper : IDisposable
    {
        #region Variables

        private string strConnectionString;
        private DbConnection objConnection;
        private DbCommand objCommand;
        private DbProviderFactory objFactory = null;
        private bool boolHandleErrors;
        private string strLastError;
        private bool boolLogError;
        private string strLogFile;

        #endregion

        #region Constructors

        /// <summary>
        /// Constructor accepts the connection string and provider.
        /// </summary>
        /// <param name="connectionstring"></param>
        /// <param name="provider"></param>
        public SqlHelper(string connectionstring, Providers provider)
        {
            strConnectionString = connectionstring;
            switch (provider)
            {
                case Providers.SqlServer:
                    objFactory = SqlClientFactory.Instance;
                    break;
                case Providers.OleDb:
                    objFactory = OleDbFactory.Instance;
                    break;
                case Providers.ODBC:
                    objFactory = OdbcFactory.Instance;
                    break;
                case Providers.ConfigDefined:
                    string providername = ConfigurationManager.ConnectionStrings["connectionstring"].ProviderName;
                    switch (providername)
                    {
                        case "System.Data.SqlClient":
                            objFactory = SqlClientFactory.Instance;
                            break;
                        case "System.Data.OleDb":
                            objFactory = OleDbFactory.Instance;
                            break;
                    }
                    break;

            }

            objConnection = objFactory.CreateConnection();

            objCommand = objFactory.CreateCommand();

            objConnection.ConnectionString = strConnectionString;

            objCommand.Connection = objConnection;
        }

        /// <summary>
        /// This constructor accepts enumeration of type Providers.This 
        /// enumeration is provided in SqlHelpercomponent itself and has the
        /// following possible values;SqlServer,OleDb,Oracle,ODBC,ConfigDefined
        /// When you specify provider as ConfigDefined the component simply 
        /// picks up the value from providerName attribute from the 
        /// connectionStrings section.
        /// Whatever provider you specify determines .NET provider used internally.
        /// </summary>
        /// <param name="provider"></param>
        public SqlHelper(Providers provider)
            : this(ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString, provider)
        {
        }

        /// <summary>
        /// This constructor accepts the connection string directly and assumes
        /// SQL Server data provider
        /// </summary>
        /// <param name="connectionstring"></param>
        public SqlHelper(string connectionstring)
            : this(connectionstring, Providers.SqlServer)
        {
        }

        /// <summary>
        /// The parameter less constructor expects that the connection will be 
        /// present in the ConnectionStrings section of configuration file.
        /// The connection string name must be connectionstring.
        /// It also expects that the connection string will have providerName defined.
        /// </summary>
        public SqlHelper()
            : this(ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString, Providers.ConfigDefined)
        {
        }

        #endregion

        #region Properties

        /// <summary>
        /// Any Exceptions raised within the SqlHelper component will not be
        /// bubbled up further if we set HandleErrors property to true
        /// </summary>
        public bool HandleErrors
        {
            get
            {
                return boolHandleErrors;
            }
            set
            {
                boolHandleErrors = value;
            }
        }

        /// <summary>
        /// To check whether any error is there ,check LastError property value
        /// </summary>
        public string LastError
        {
            get
            {
                return strLastError;
            }
        }

        /// <summary>
        /// The LogErrors and LogFile properties go hand in hand.
        /// If you set LogErrors property to true then you must also
        /// specify the LogFile property.
        /// </summary>
        public bool LogErrors
        {
            get
            {
                return boolLogError;
            }
            set
            {
                boolLogError = value;
            }
        }
        /// <summary>
        /// When LogErrors property is set to true any exceptions raised from 
        /// the SqlHelper component will be logged in the file specified
        /// using LogFile property.
        /// </summary>
        public string LogFile
        {
            get
            {
                return strLogFile;
            }
            set
            {
                strLogFile = value;
            }
        }

        /// <summary>
        /// The Command property gives you raw access to the command object 
        /// used by the component.
        /// You can use this property  to fine tune specifice properties of 
        /// command,parameters and connection objects.
        /// </summary>
        /// 
        public DbCommand Command
        {
            get
            {
                return objCommand;
            }
        }

        #endregion

        #region Enumerations

        /// <summary>
        /// The Providers enumeration is used to indicate the .NET data provider
        /// to be used for instantiating various objects and executing queries.
        /// </summary>
        public enum Providers
        {
            /// <summary>
            /// Provider for SqlServer
            /// </summary>
            SqlServer,
            /// <summary>
            /// Provider for OleDb
            /// </summary>
            OleDb,
            /// <summary>
            /// Provider for Oracle
            /// </summary>
            Oracle,
            /// <summary>
            /// Provider for ODBC
            /// </summary>
            ODBC,
            /// <summary>
            /// Provider for default
            /// </summary>
            ConfigDefined
        }

        /// <summary>
        /// The ConnectionState enumeration is used to indicate whether the 
        /// underlying connection be kept open or not after exceuting a query.
        /// </summary>
        public enum ConnectionState
        {
            /// <summary>
            /// If ConnectionState to be kept open
            /// </summary>
            KeepOpen,
            /// <summary>
            /// If ConnectionState to be closed
            /// </summary>
            CloseOnExit
        }

        #endregion

        #region AddParameter

        /// <summary>
        /// Adds a parameter to the command being executed.
        /// Accepts parameter name and its value
        /// </summary>
        /// <param name="name"></param>
        /// <param name="value"></param>
        /// <param name="dbType"></param>
        /// <returns></returns>
        public int AddParameter(string name, object value, SqlDbType dbType)
        {
            SqlParameter Param = new SqlParameter();
            Param.ParameterName = name;
            Param.SqlDbType = dbType;
            Param.Value = value;
            return objCommand.Parameters.Add(Param);
        }

        /// <summary>
        /// Adds a parameter to the command being executed.
        /// Accepts parameter name and its value with Size
        /// </summary>
        /// <param name="name"></param>
        /// <param name="value"></param>
        /// <param name="dbType"></param>
        /// <param name="size"></param>
        /// <returns></returns>
        public int AddParameter(string name, object value, SqlDbType dbType, int size)
        {
            SqlParameter Param = new SqlParameter();
            Param.ParameterName = name;
            Param.SqlDbType = dbType;
            Param.Size = size;
            Param.Value = value;
            return objCommand.Parameters.Add(Param);
        }

        /// <summary>
        /// Adds an output parameter to the command being executed.
        /// Accepts parameter name and its value
        /// </summary>
        /// <param name="name"></param>
        /// <param name="dbType"></param>
        /// <param name="parameterSize"></param>
        /// <returns></returns>
        public int AddOutputParameter(string name, SqlDbType dbType, int parameterSize)
        {
            SqlParameter Param = new SqlParameter();
            Param.Size = parameterSize;
            Param.ParameterName = name;
            Param.SqlDbType = dbType;
            Param.Direction = ParameterDirection.Output;
            return objCommand.Parameters.Add(Param);
        }
        /// <summary>
        /// Adds an output parameter to the command being executed for decimals 
        /// </summary>
        /// <param name="name"></param>
        /// <param name="dbType"></param>
        /// <param name="Precision"></param>
        /// <param name="Scale"></param>
        /// <returns></returns>
        public int AddOutputParameter(string name, SqlDbType dbType, byte Precision, byte Scale)
        {
            SqlParameter Param = new SqlParameter();
            Param.ParameterName = name;
            Param.SqlDbType = dbType;
            Param.Direction = ParameterDirection.Output;
            Param.Precision = Precision;
            Param.Scale = Scale;
            return objCommand.Parameters.Add(Param); ;
        }
        /// <summary>
        /// Adds an InputOutput parameter to the command being executed.
        /// Accepts parameter name and its value
        /// </summary>
        /// <param name="name"></param>
        /// <param name="value"></param>
        /// <param name="dbType"></param>
        /// <returns></returns>
        public int AddInputOutputParameter(string name, object value, SqlDbType dbType)
        {
            SqlParameter Param = new SqlParameter();
            Param.ParameterName = name;
            Param.SqlDbType = dbType;
            Param.Value = value;
            Param.Direction = ParameterDirection.InputOutput;
            return objCommand.Parameters.Add(Param);
        }

        /// <summary>
        /// Adds an output parameter to the command being executed.
        /// Accepts parameter name and its value
        /// </summary>
        /// <param name="name"></param>
        /// <param name="dbType"></param>
        /// <returns></returns>
        public int AddOutputParameter(string name, SqlDbType dbType)
        {
            SqlParameter Param = new SqlParameter();
            Param.ParameterName = name;
            Param.SqlDbType = dbType;
            Param.Direction = ParameterDirection.Output;
            return objCommand.Parameters.Add(Param);
        }

        /// <summary>
        /// Adds a parameter to the command being executed.
        /// Accepts parameter object (Instance of SqlParameter,OleDbParameter,
        /// OracleParameter or ODBCParameter class)
        /// </summary>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public int AddParameter(DbParameter parameter)
        {
            return objCommand.Parameters.Add(parameter);
        }

        #endregion

        #region Transactions

        /// <summary>
        /// Initiates a transaction
        /// </summary>
        public void BeginTransaction()
        {
            if (objConnection.State == System.Data.ConnectionState.Closed)
            {
                objConnection.Open();
            }
            //objCommand.Transaction = objConnection.BeginTransaction(IsolationLevel.Snapshot);
            Command.Transaction = objConnection.BeginTransaction();

        }

        /// <summary>
        /// Commits the active transaction
        /// </summary>
        public void CommitTransaction()
        {
            if (objCommand.Transaction != null)
            {
                objCommand.Transaction.Commit();
            }
            objConnection.Close();
        }

        /// <summary>
        /// Cancels the active transaction
        /// </summary>
        public void RollbackTransaction()
        {
            if (objCommand.Transaction != null)
            {
                objCommand.Transaction.Rollback();

            }

            if (objConnection.State != System.Data.ConnectionState.Closed)
            {
                objConnection.Close();
            }

        }

        #endregion

        #region ExecuteNonQuery

        /// <summary>
        /// This overloaded method accepts an SQL query directly.
        /// Once the query is executed ,the undelying connection is closed.
        /// </summary>
        /// <param name="query"></param>
        /// <returns>No: of records affected by the query</returns>
        public int ExecuteNonQuery(string query)
        {
            return ExecuteNonQuery(query, CommandType.Text);
        }

        /// <summary>
        /// This overloaded method accepts an SQL query or stored procedure and CommandType
        /// Once the query is executed ,the undelying connection is closed
        /// </summary>
        /// <param name="query"></param>
        /// <param name="commandtype"></param>
        /// <returns>No: of records affected by the query</returns>
        public int ExecuteNonQuery(string query, CommandType commandtype)
        {
            if (this.Command.Transaction == null)
            {
                return ExecuteNonQuery(query, commandtype, ConnectionState.CloseOnExit);
            }
            else
            {
                return ExecuteNonQuery(query, commandtype, ConnectionState.KeepOpen);
            }
        }

        /**************New Change for the CLR PROCEDURES to work , create a new method to increase the Time out.***************/

        /// <summary>
        /// This overloaded method accepts an SQL query or stored procedure and CommandType
        /// Once the query is executed ,the undelying connection is closed
        /// </summary>
        /// <param name="query"></param>
        /// <param name="commandtype"></param>
        /// <returns>No: of records affected by the query</returns>
        public int ExecuteNonQueryLongRequest(string query, CommandType commandtype)
        {
            return ExecuteNonQueryLongRequest(query, commandtype, ConnectionState.CloseOnExit);
        }

        /// <summary>
        /// Allows you to specify query,CommandType and ConnectionState
        /// </summary>
        /// <param name="query"></param>
        /// <param name="commandtype"></param>
        /// <param name="connectionstate"></param>
        /// <returns>No: of records affected by the query</returns>
        public int ExecuteNonQueryLongRequest(string query, CommandType commandtype, ConnectionState connectionstate)
        {
            objCommand.CommandText = query;
            objCommand.CommandType = commandtype;
            objCommand.CommandTimeout = 10000;
            int i = -1;
            try
            {
                if (objConnection.State == System.Data.ConnectionState.Closed)
                {
                    objConnection.Open();
                }
                i = objCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                HandleExceptions(ex);
            }
            finally
            {
                //objCommand.Parameters.Clear();/*To return output parameters*/
                if (connectionstate == ConnectionState.CloseOnExit)
                {
                    objConnection.Close();
                }
            }

            return i;
        }
        /****************************************************************************************************************************/

        /// <summary>
        /// This overload allows you to pass an SQL query and indicate whether
        /// the underlying connection be kept open or not.
        /// The ConnectionState enumeration is defined in SqlHelper itself and
        /// has two possible values-KeepOpen and CloseOnExit.
        /// KeepOpen   - keeps the underlying conection open even after command 
        ///              execution is complete 
        /// CloseOnExit- closes the underlying connection
        /// </summary>
        /// <param name="query"></param>
        /// <param name="connectionstate"></param>
        /// <returns>No: of records affected by the query</returns>
        public int ExecuteNonQuery(string query, ConnectionState connectionstate)
        {
            return ExecuteNonQuery(query, CommandType.Text, connectionstate);
        }

        /// <summary>
        /// Allows you to specify query,CommandType and ConnectionState
        /// </summary>
        /// <param name="query"></param>
        /// <param name="commandtype"></param>
        /// <param name="connectionstate"></param>
        /// <returns>No: of records affected by the query</returns>
        public int ExecuteNonQuery(string query, CommandType commandtype, ConnectionState connectionstate)
        {
            objCommand.CommandText = query;
            objCommand.CommandType = commandtype;
            int i = -1;
            try
            {
                if (objConnection.State == System.Data.ConnectionState.Closed)
                {
                    objConnection.Open();
                }
                i = objCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                HandleExceptions(ex);
            }
            finally
            {
                //objCommand.Parameters.Clear();/*To return output parameters*/
                if (connectionstate == ConnectionState.CloseOnExit)
                {
                    objConnection.Close();
                }
            }

            return i;
        }

        #endregion

        #region ExecuteScalar

        /// <summary>
        /// Allows you to execute SELECT queries and stored procedures that 
        /// return a single value.
        /// Once the query is exceuted the underlying conncetion is closed
        /// </summary>
        /// <param name="query"></param>
        /// <returns>Returns an object representing the single value</returns>
        public object ExecuteScalar(string query)
        {
            return ExecuteScalar(query, CommandType.Text, ConnectionState.CloseOnExit);
        }

        /// <summary>
        /// This overload to ExecuteScalar accepts query or SP name 
        /// and CommandType as parameter
        /// </summary>
        /// <param name="query"></param>
        /// <param name="commandtype"></param>
        /// <returns></returns>
        public object ExecuteScalar(string query, CommandType commandtype)
        {
            return ExecuteScalar(query, commandtype, ConnectionState.CloseOnExit);
        }

        /// <summary>
        /// This overload allows you to pass an SQL query and indicate whether
        /// the underlying connection be kept open or not.
        /// The ConnectionState enumeration is defined in SqlHelper itself and
        /// has two possible values-KeepOpen and CloseOnExit.
        /// KeepOpen   - keeps the underlying conection open even after command 
        ///              execution is complete 
        /// CloseOnExit- closes the underlying connection
        /// </summary>
        /// <param name="query"></param>
        /// <param name="connectionstate"></param>
        /// <returns></returns>
        public object ExecuteScalar(string query, ConnectionState connectionstate)
        {
            return ExecuteScalar(query, CommandType.Text, connectionstate);
        }

        /// <summary>
        /// This overload allows you to specify query,CommandType and ConnectionState
        /// </summary>
        /// <param name="query"></param>
        /// <param name="commandtype"></param>
        /// <param name="connectionstate"></param>
        /// <returns></returns>
        public object ExecuteScalar(string query, CommandType commandtype, ConnectionState connectionstate)
        {
            objCommand.CommandText = query;
            objCommand.CommandType = commandtype;
            object o = null;
            try
            {
                if (objConnection.State == System.Data.ConnectionState.Closed)
                {
                    objConnection.Open();
                }
                o = objCommand.ExecuteScalar();
            }
            catch (Exception ex)
            {
                HandleExceptions(ex);
            }
            finally
            {
                //objCommand.Parameters.Clear();/*To return output parameters*/
                if (connectionstate == ConnectionState.CloseOnExit)
                {
                    objConnection.Close();
                }
            }

            return o;
        }

        #endregion

        #region ExecuteReader

        /// <summary>
        /// Allows you to execute SELECT queries and stored procedures with a DataReader.
        /// Once the returned DataReader object is closed,the connection is closed.
        /// </summary>
        /// <param name="query"></param>
        /// <returns>Returns a DataReader</returns>
        public DbDataReader ExecuteReader(string query)
        {
            return ExecuteReader(query, CommandType.Text, ConnectionState.CloseOnExit);
        }

        /// <summary>
        /// This overlaosd accepts query or SP name and CommandType
        /// </summary>
        /// <param name="query"></param>
        /// <param name="commandtype"></param>
        /// <returns>Returns a DataReader</returns>
        public DbDataReader ExecuteReader(string query, CommandType commandtype)
        {
            if (this.Command.Transaction == null)
            {
                return ExecuteReader(query, commandtype, ConnectionState.CloseOnExit);
            }
            else
            {
                return ExecuteReader(query, commandtype, ConnectionState.KeepOpen);

            }
        }

        /// <summary>
        /// This overload allows you to pass an SQL query and indicate whether
        /// the underlying connection is kept open or not.
        /// The ConnectionState enumeration is defined in SqlHelper itself
        /// and has two possible values-KeepOpen and CloseOnExit.
        /// KeepOpen    - keeps the underlying conection open even after command
        ///               execution is complete 
        /// CloseOnExit - closes the underlying connection
        /// </summary>
        /// <param name="query"></param>
        /// <param name="connectionstate"></param>
        /// <returns>Returns a DataReader</returns>
        public DbDataReader ExecuteReader(string query, ConnectionState connectionstate)
        {
            return ExecuteReader(query, CommandType.Text, connectionstate);
        }

        /// <summary>
        /// This overload allows you to specify query,CommandType and ConnectionState
        /// </summary>
        /// <param name="query"></param>
        /// <param name="commandtype"></param>
        /// <param name="connectionstate"></param>
        /// <returns>Returns a DataReader</returns>
        public DbDataReader ExecuteReader(string query, CommandType commandtype, ConnectionState connectionstate)
        {
            objCommand.CommandText = query;
            objCommand.CommandType = commandtype;
            //  objCommand.CommandTimeout = ApplicationSettingsDTO.CommandTimeoutPeriod;


            DbDataReader reader = null;
            try
            {
                if (objConnection.State == System.Data.ConnectionState.Closed)
                {
                    objConnection.Open();
                }
                if (connectionstate == ConnectionState.CloseOnExit)
                {
                    reader = objCommand.ExecuteReader(CommandBehavior.CloseConnection);
                }
                else
                {
                    reader = objCommand.ExecuteReader();
                }

            }
            catch (Exception ex)
            {
                HandleExceptions(ex);
            }
            finally
            {
                //objCommand.Parameters.Clear();/*To return output parameters*/
            }

            return reader;
        }

        #endregion

        #region ExecuteDataSet

        /// <summary>
        /// Allows you to execute an SQL query.
        /// Once the query is exceuted the underlying connection is closed.
        /// </summary>
        /// <param name="query"></param>
        /// <returns>Returns a DataSet</returns>
        public DataSet ExecuteDataSet(string query)
        {
            return ExecuteDataSet(query, CommandType.Text, ConnectionState.CloseOnExit);
        }

        /// <summary>
        /// This overload accepts query or SP name and CommandType as parameter.
        /// Once the query is exceuted the underlying connection is closed.
        /// </summary>
        /// <param name="query"></param>
        /// <param name="commandtype"></param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string query, CommandType commandtype)
        {
            return ExecuteDataSet(query, commandtype, ConnectionState.CloseOnExit);
        }

        /// <summary>
        /// This overload allows you to pass an SQL query and indicate whether 
        /// the underlying connection is kept open or not.
        /// The ConnectionState enumeration is defined in SqlHelper itself
        /// and has two possible values-KeepOpen and CloseOnExit.
        /// KeepOpen   - keeps the underlying conection open even after command 
        ///              execution is complete 
        /// CloseOnExit- closes the underlying connection
        /// </summary>
        /// <param name="query"></param>
        /// <param name="connectionstate"></param>
        /// <returns>DataSet</returns>
        public DataSet ExecuteDataSet(string query, ConnectionState connectionstate)
        {
            return ExecuteDataSet(query, CommandType.Text, connectionstate);
        }

        /// <summary>
        /// This overload accepts query,CommandType and ConnectionState
        /// </summary>
        /// <param name="query"></param>
        /// <param name="commandtype"></param>
        /// <param name="connectionstate"></param>
        /// <returns>DataSet</returns>
        public DataSet ExecuteDataSet(string query, CommandType commandtype, ConnectionState connectionstate)
        {
            DbDataAdapter adapter = objFactory.CreateDataAdapter();
            objCommand.CommandText = query;
            objCommand.CommandType = commandtype;
            adapter.SelectCommand = objCommand;
            DataSet ds = new DataSet();
            try
            {
                adapter.Fill(ds);
            }
            catch (Exception ex)
            {
                HandleExceptions(ex);
            }
            finally
            {
                ////objCommand.Parameters.Clear();
                if (connectionstate == ConnectionState.CloseOnExit)
                {
                    if (objConnection.State == System.Data.ConnectionState.Open)
                    {
                        objConnection.Close();
                    }
                }
            }
            return ds;
        }

        #endregion

        #region ExecuteDataTable

        /// <summary>
        /// Allows you to execute SELECT queries and stored procedures.
        /// Once the query is exceuted the underlying conncetion is closed.
        /// </summary>
        /// <param name="query"></param>
        /// <returns>DataTable</returns>
        public DataTable ExecuteDataTable(string query)
        {
            return ExecuteDataTable(query, CommandType.Text, ConnectionState.CloseOnExit);
        }

        /// <summary>
        /// This overload accepts query or SP name and CommandType as parameter.
        /// Once the query is exceuted the underlying connection is closed.
        /// </summary>
        /// <param name="query"></param>
        /// <param name="commandtype"></param>
        /// <returns>DataTable</returns>
        public DataTable ExecuteDataTable(string query, CommandType commandtype)
        {
            return ExecuteDataTable(query, commandtype, ConnectionState.CloseOnExit);
        }
        /// <summary>
        /// This overload allows you to pass an SQL query and indicate whether 
        /// the underlying connection is kept open or not.
        /// The ConnectionState enumeration is defined in SqlHelper itself
        /// and has two possible values-KeepOpen and CloseOnExit.
        /// KeepOpen   - keeps the underlying conection open even after command 
        ///              execution is complete 
        /// CloseOnExit- closes the underlying connection
        /// </summary>
        /// <param name="query"></param>
        /// <param name="connectionstate"></param>
        /// <returns>DataTable</returns>
        public DataTable ExecuteDataTable(string query, ConnectionState connectionstate)
        {
            return ExecuteDataTable(query, CommandType.Text, connectionstate);
        }
        /// <summary>
        /// This overload accepts query,CommandType and ConnectionState
        /// </summary>
        /// <param name="query"></param>
        /// <param name="commandtype"></param>
        /// <param name="connectionstate"></param>
        /// <returns>DataTable</returns>
        public DataTable ExecuteDataTable(string query, CommandType commandtype, ConnectionState connectionstate)
        {
            DbDataAdapter adapter = objFactory.CreateDataAdapter();
            objCommand.CommandText = query;
            objCommand.CommandType = commandtype;
            adapter.SelectCommand = objCommand;
            DataTable dt = new DataTable();
            try
            {
                adapter.Fill(dt);
            }
            catch (Exception ex)
            {
                HandleExceptions(ex);
            }
            finally
            {
                //objCommand.Parameters.Clear();/*To return output parameters*/
                if (connectionstate == ConnectionState.CloseOnExit)
                {
                    if (objConnection.State == System.Data.ConnectionState.Open)
                    {
                        objConnection.Close();
                    }
                }
            }

            return dt;
        }

        #endregion

        #region Exception Handling

        private void HandleExceptions(Exception ex)
        {
            if (LogErrors)
            {
                WriteToLog(ex.Message);
            }
            if (HandleErrors)
            {
                strLastError = ex.Message;
            }
            else
            {
                throw ex;
            }
        }
        /// <summary>
        /// Exceptions raised were logged in the file specified using 
        /// LogFile property.
        /// </summary>
        /// <param name="msg"></param>
        private void WriteToLog(string msg)
        {
            StreamWriter writer = File.AppendText(LogFile);
            writer.WriteLine(DateTime.Now.ToString() + " - " + msg);
            writer.Close();
        }

        #endregion

        #region Release Resources
        /// <summary>
        /// Release the resources for the unmanaged code inside the class.
        /// objConnection.Dispose simply forgets the state of the connection and closes it
        /// Dispose clears the connection pool.
        /// </summary>
        public void Dispose()
        {

            if (objConnection.State == System.Data.ConnectionState.Open)
            {
                objConnection.Close();
            }
            objConnection.Dispose();
            objCommand.Dispose();
        }
        #endregion

        /// <summary>
        /// Clears parameter array
        /// </summary>
        public void ClearParameters()
        {
            this.Command.Parameters.Clear();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="A"></param>
        /// <param name="B"></param>
        /// <returns></returns>
        private bool ColumnEqual(object A, object B)
        {
            if (A == DBNull.Value && B == DBNull.Value) //  both are DBNull.Value
                return true;
            if (A == DBNull.Value || B == DBNull.Value) //  only one is DBNull.Value
                return false;
            return (A.Equals(B));  // value type standard comparison
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="SourceTable"></param>
        /// <param name="FieldName"></param>
        /// <returns></returns>
        public DataTable SelectDistinct(string TableName, DataTable SourceTable, string FieldName)
        {
            DataTable dt = new DataTable(TableName);
            dt.Columns.Add(FieldName, SourceTable.Columns[FieldName].DataType);

            object LastValue = null;
            foreach (DataRow dr in SourceTable.Select("", FieldName))
            {
                if (LastValue == null || !(ColumnEqual(LastValue, dr[FieldName])))
                {
                    LastValue = dr[FieldName];
                    dt.Rows.Add(new object[] { LastValue });
                }
            }

            return dt;
        }



    }
}